package com.moleq.service;

import java.util.ArrayList;
import java.util.List;

import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.moleq.db.DatabaseHelper;
import com.moleq.db.DatabaseSchema;
import com.moleq.models.ImportData;
import com.moleq.models.ItemSetting;
import com.moleq.models.NetWT;
import com.moleq.network.CommTask;

public class ItemSettingService {
	Context mContext;
	private DatabaseHelper DBHelper;
	private SQLiteDatabase db;
	private static final int PageSize = 25;

	public ItemSettingService(Context context) {
		this.DBHelper = new DatabaseHelper(context);
		this.mContext = context;
	}

	public ItemSettingService(String shareDBPath) {
		this.db = SQLiteDatabase.openOrCreateDatabase(shareDBPath, null);
	}

	public class SP {
		public static final int ALL = 0;
		public static final int UPC = 1;
		public static final int Department = 2;
		public static final int SubDept = 3;
		public static final int Vendor = 4;
	}

	public int GetPageCountSize(int SPType, String searchString) {
		String countSql = "select count(*) from t_item_setting as a,t_uom_desc as b where a.F23 = b.F23 ";
		switch (SPType) {
		case SP.ALL:
			break;
		case SP.UPC:
			countSql += " and a.F01 like '" + searchString + "%'";
			break;
		case SP.Department:
			countSql += " and a.F03 in (select sd_id from t_sub_dept_setting as b where b.F03 = "
					+ searchString + ")";
			break;
		case SP.SubDept:
			countSql += " and a.F03 = " + searchString;
			break;
		case SP.Vendor:
			countSql += " and a.F27 = '" + searchString + "'";
			break;
		}
		int PageCountSize = 0;
		db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(countSql, null);
		if (cursor == null) {
			return 0;
		}
		cursor.moveToLast();
		long ItemCount = cursor.getLong(0);
		long page = ItemCount % PageSize;
		if (page == 0) {
			PageCountSize = (int) ((ItemCount / PageSize));
		} else {
			PageCountSize = (int) ((ItemCount / PageSize) + 1);
		}
		cursor.close();
		return PageCountSize;
	}

	public List<ItemSetting> SearchItems(int page, int searchType,
			String searchString) {
		String sql = "select a.*,b.ud_short from t_item_setting as a,t_uom_desc as b where a.F23 = b.F23 ";
		switch (searchType) {
		case SP.ALL:
			sql += "order by a.F01";
			sql += " Limit " + String.valueOf(PageSize) + " Offset "
					+ String.valueOf(page * PageSize);
			break;
		case SP.UPC:
			sql += " and a.F01 like '" + searchString + "%'"
					+ " order by a.F01 Limit " + String.valueOf(PageSize)
					+ " Offset " + String.valueOf(page * PageSize);
			break;
		case SP.Department:
			sql += " and a.F03 in (select sd_id from t_sub_dept_setting as b where b.F03 = "
					+ searchString
					+ ")"
					+ " order by a.F01 Limit "
					+ String.valueOf(PageSize)
					+ " Offset "
					+ String.valueOf(page * PageSize);
			break;
		case SP.SubDept:
			sql += " and a.F03 = " + searchString + " order by a.F01 Limit "
					+ String.valueOf(PageSize) + " Offset "
					+ String.valueOf(page * PageSize);
			break;
		case SP.Vendor:
			sql += " and a.F27 = '" + searchString + "'"
					+ " order by a.F01 Limit " + String.valueOf(PageSize)
					+ " Offset " + String.valueOf(page * PageSize);
			break;
		}
		return findItems(sql);
	}

	public List<ItemSetting> findItems(String sql) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		List<ItemSetting> Source = new ArrayList<ItemSetting>();
		if (cursor == null) {
			return Source;
		}
		while (cursor.moveToNext()) {
			ItemSetting itemSetting = new ItemSetting();
			itemSetting.setUpc(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_UPC)));
			itemSetting.setName(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.DESCRIPTOR)));
			itemSetting
					.setDep(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.DEPARTMENT_NUMBER)));
			itemSetting
					.setSubDep(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.SUB_DEPARTMENT_NUMBER)));
			itemSetting
					.setActive(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_STATUS_INDICATOR)));
			itemSetting
					.setCasePack(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CASE_PACK_SIZE)));
			itemSetting
					.setNetWT(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_OF_MEASURE)));
			itemSetting
					.setWt(cursor.getDouble(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR)));
			itemSetting.setVendor(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.VENDOR_NUMBER)));
			itemSetting
					.setDesc(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.EXPANDED_DESCRIPTION)));
			itemSetting.setPrice(cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE)));
			itemSetting
					.setMultple(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY)));
			itemSetting.setCaseCost(cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.CASE_COST)));
			itemSetting.setFoodStamp(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.FOOD_STAMP)));
			itemSetting.setTax1(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX1)));
			itemSetting
					.setScaleWeight(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.SCALE_OR_WEIGHT)));
			itemSetting.setWic(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.WIC_ELIGIBLE)));
			itemSetting
					.setCvv(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CVV_ELIGIBILITY)));
			itemSetting
					.setCreate_datetime(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CREATE_DATETIME)));
			itemSetting
					.setUpdate_datetime(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UPDATE_DATETIME)));
			itemSetting
					.setFollowdep(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.FOLLOW_DEPARTMENT)));
			itemSetting.setTax2(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX2)));
			itemSetting.setTax3(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX3)));
			itemSetting.setTax4(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX4)));
			itemSetting.setTax5(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX5)));
			itemSetting.setTax6(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX6)));
			itemSetting
					.setItem_discount_flag(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_DISCOUNT_FLAG)));
			itemSetting
					.setBottle_deposit(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.BOTTLE_DEPOSIT)));
			itemSetting.setIs_age_ver(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_AGE_VER)));
			itemSetting.setIs_tare(cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_TARE)));
			itemSetting.setIs_loyalty(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_LOYALTY)));
			itemSetting.setIs_coupon(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_COUPON)));
			itemSetting.setService_fl(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.SERVICE_FL)));
			itemSetting.setPrinter_id(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.PRINTER_ID)));
			itemSetting.setIs_mult_coupon(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_MULT_COUPON)));
			String wt = cursor.getString(cursor.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR));
			itemSetting.setNetWtText((wt == null || wt.equals("0")?"":(wt + " "))
					+ cursor.getString(35));
			Source.add(itemSetting);
		}
		cursor.close();
		return Source;
	}

	public ItemSetting findByUpc(String upc) {
//		String sql = "select * from t_item_setting where F01 = '" + upc + "'";
		String sql = "select a.*,b.ud_short from t_item_setting as a,t_uom_desc as b where a.F23 = b.F23 and F01 = '" + upc + "'";
		
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		ItemSetting itemSetting = new ItemSetting();
		if (cursor == null) {
			// ShowMessage(DBError.error,mMessageDialog.MESSAGE_BOX_OK,mMessageDialog.ICON_ERROR);
			// MessageBox.show(mContext, DBError.error);
			// DBError.error = "";
			return null;
		}
		if (cursor.moveToNext()) {
			itemSetting.setUpc(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_UPC)));
			itemSetting.setName(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.DESCRIPTOR)));
			itemSetting
					.setDep(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.DEPARTMENT_NUMBER)));
			itemSetting
					.setSubDep(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.SUB_DEPARTMENT_NUMBER)));
			itemSetting
					.setActive(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_STATUS_INDICATOR)));
			itemSetting
					.setCasePack(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CASE_PACK_SIZE)));
			itemSetting
					.setNetWT(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_OF_MEASURE)));
			itemSetting
					.setWt(cursor.getDouble(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR)));
			itemSetting.setVendor(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.VENDOR_NUMBER)));
			itemSetting
					.setDesc(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.EXPANDED_DESCRIPTION)));
			itemSetting.setPrice(cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE)));
			itemSetting
					.setMultple(cursor.getInt(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY)));
			itemSetting.setCaseCost(cursor.getDouble(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.CASE_COST)));
			itemSetting.setFoodStamp(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.FOOD_STAMP)));
			itemSetting.setTax1(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX1)));
			itemSetting
					.setScaleWeight(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.SCALE_OR_WEIGHT)));
			itemSetting.setWic(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.WIC_ELIGIBLE)));
			itemSetting
					.setCvv(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CVV_ELIGIBILITY)));
			itemSetting
					.setCreate_datetime(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.CREATE_DATETIME)));
			itemSetting
					.setUpdate_datetime(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UPDATE_DATETIME)));
			itemSetting
					.setFollowdep(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.FOLLOW_DEPARTMENT)));
			itemSetting.setTax2(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX2)));
			itemSetting.setTax3(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX3)));
			itemSetting.setTax4(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX4)));
			itemSetting.setTax5(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX5)));
			itemSetting.setTax6(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.TAX6)));
			itemSetting
					.setItem_discount_flag(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.ITEM_DISCOUNT_FLAG)));
			itemSetting
					.setBottle_deposit(cursor.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.BOTTLE_DEPOSIT)));
			itemSetting.setIs_age_ver(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_AGE_VER)));
			itemSetting.setIs_tare(cursor.getInt(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_TARE)));
			itemSetting.setIs_loyalty(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_LOYALTY)));
			itemSetting.setIs_coupon(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_COUPON)));
			itemSetting.setService_fl(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.SERVICE_FL)));
			itemSetting.setPrinter_id(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.PRINTER_ID)));
			itemSetting.setIs_mult_coupon(cursor.getString(cursor
					.getColumnIndex(DatabaseSchema.ItemSetting.IS_MULT_COUPON)));
			String wt = cursor
					.getString(cursor
							.getColumnIndex(DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR));
			itemSetting.setNetWtText((wt == null || wt.equals("0") ? "" : (wt + " "))
					+ cursor.getString(35));
		} else {
			itemSetting = null;
		}
		cursor.close();
		return itemSetting;
	}

	public int updatePrice(String price, String amt, String upc) {
		if (amt.equals("")) {
			amt = "1";
		}

		String sql = "update t_item_setting set F30 =" + RoundUp(price)
				+ ",F31 = " + amt + " where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(sql);

		return 1;
	}

	public String RoundUp(String value) {
		float f = Float.valueOf(value);
		f = Math.round(f * 100) / 100f;
		return String.valueOf(f);
	}

	public void processCommTask(CommTask task) {
		// System.out.println("process task building.");
		List<ItemSetting> lists = AnalysisFile(task.getActions());
		if (lists.size() > 0) {
			for (int i = 0; i < lists.size(); i++) {
				ItemSetting itemSetting = lists.get(i);
				if (isExist(itemSetting.getUpc())) {
					// update
					update(itemSetting);
				} else {
					// insert
					insert(itemSetting);
				}
			}

		}
	}

	public void save(ItemSetting itemSetting) {
		if (isExist(itemSetting.getUpc())) {
			// update
			update(itemSetting);
		} else {
			// insert
			insert(itemSetting);
		}
	}

	public void update(ItemSetting obj) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"Update " + DatabaseSchema.ItemSetting.SCHEMA_NAME
						+ " Set F02 = ?,F03 = ?,F04 = ?,F08 = ?,F19 = ?"
						+ ",F23 = ?,F24 = ?,F27 = ?,F29 = ?,F30 = ?,F31 = ?"
						+ ",F38 = ?,F79 = ?,F81 = ?,F82 = ?,F178 = ?,F924 = ?"
						+ ",update_datetime = strftime('%Y%m%d%H%M%S','now')"
						+ ",F106 = ?,F96 = ?,F97 = ?,F98 = ?,F150 = ?,F158 = ?"
						+ ",is_loyalty = ?,is_tare = ?,is_age_ver = ?,F99 = ?"
						+ ",F100 = ?,is_coupon = ?,service_fl = ?,printer_id = ?,is_mult_coupon = ? where F01 = ?",
				new Object[] { obj.getName(), obj.getDep(), (obj.getSubDep() == -1?null:obj.getSubDep()),
						obj.getActive(), (obj.getCasePack() == -1?null:obj.getCasePack()), obj.getNetWT(),
						obj.getWt(), obj.getVendor(), obj.getDesc(),
						obj.getPrice(), obj.getMultple(), (obj.getCaseCost() == -1?null:obj.getCaseCost()),
						obj.getFoodStamp(), obj.getTax1(),
						obj.getScaleWeight(), obj.getWic(), obj.getCvv(),
						obj.getFollowdep(), obj.getTax2(), obj.getTax3(),
						obj.getTax4(), obj.getItem_discount_flag(),
						obj.getBottle_deposit(), obj.getIs_loyalty(),
						obj.getIs_tare(), obj.getIs_age_ver(), obj.getTax5(),
						obj.getTax6(), obj.getIs_coupon(),obj.getService_fl(),obj.getPrinter_id(),obj.getIs_mult_coupon(), obj.getUpc() });
	}

	public void insert(ItemSetting obj) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		 db.execSQL(
		 "INSERT INTO "
		 + DatabaseSchema.ItemSetting.SCHEMA_NAME
		 + " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?"
		 +
		 ",?,?,?,strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
		 new Object[] { obj.getUpc(), obj.getName(), obj.getDep(),
				 (obj.getSubDep() == -1?null:obj.getSubDep()), obj.getActive(),(obj.getCasePack() == -1?null:obj.getCasePack()),
		 obj.getNetWT(), obj.getWt(), obj.getVendor(),
		 obj.getDesc(), obj.getPrice(), obj.getMultple(),
						(obj.getCaseCost() == -1 ? null : obj.getCaseCost()), obj.getFoodStamp(), obj.getTax1(),
		 obj.getScaleWeight(), obj.getWic(), obj.getCvv(),
		 obj.getFollowdep(), obj.getTax2(), obj.getTax3(),
		 obj.getTax4(), obj.getItem_discount_flag(),
		 obj.getBottle_deposit(), obj.getIs_loyalty(),
		 obj.getIs_tare(), obj.getIs_age_ver(), obj.getTax5(),
		 obj.getTax6(), obj.getIs_coupon(),obj.getService_fl(),obj.getPrinter_id(),obj.getIs_mult_coupon() });
	}

	public boolean isExist(String upc) {
		String sql = "select * from t_item_setting where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null || !cursor.moveToNext()) {
			cursor.close();
			return false;
		}
		cursor.close();
		return true;
	}

	public List<ItemSetting> AnalysisFile(List<String> actions) {
		List<ItemSetting> lists = new ArrayList<ItemSetting>();
		String action;
		for (int i = 0; i < actions.size(); i++) {
			action = actions.get(i);
			String[] column = action.split(",");
			ItemSetting itemSetting = new ItemSetting();
			itemSetting.setUpc(column[1]);
			itemSetting.setName(column[2]);
			itemSetting.setDep(Integer.valueOf(column[3]));
			itemSetting.setSubDep(column[4]);
			itemSetting.setActive(column[5]);
			itemSetting.setCasePack(column[6]);
			itemSetting.setNetWT(column[7]);
			itemSetting.setWt(column[8]);
			itemSetting.setVendor(column[9]);
			itemSetting.setDesc(column[10]);
			itemSetting.setPrice(Double.valueOf(column[11]));
			itemSetting.setMultple(column[12]);
			itemSetting.setCaseCost(column[13]);
			itemSetting.setFoodStamp(column[14]);
			itemSetting.setTax1(column[15]);
			// scaleWeight//column[16]
			itemSetting.setScaleWeight(column[16]);
			itemSetting.setWic(column[17]);
			itemSetting.setCvv(column[18]);
			itemSetting.setCreate_datetime(column[19]);
			itemSetting.setUpdate_datetime(column[20]);
			// followdep//column[21]
			itemSetting.setFollowdep(column[21]);
			itemSetting.setTax2(column[22]);
			itemSetting.setTax3(column[23]);
			itemSetting.setTax4(column[24]);
			itemSetting.setItem_discount_flag(column[25]);
			itemSetting.setBottle_deposit(column[26]);
			itemSetting.setIs_loyalty(column[27]);
			itemSetting.setIs_tare(Integer.valueOf(column[28]));
			itemSetting.setIs_age_ver(column[29]);
			itemSetting.setTax5(column[30]);
			itemSetting.setTax6(column[31]);
			itemSetting.setIs_coupon(column[32]);
			lists.add(itemSetting);
		}
		return lists;
	}

	public int CheckMMSetting(String upc) {
		String sql = "select * from t_mix_match where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return 0;
		if (cursor.moveToNext()) {
			cursor.close();
			return 1;
		}
		cursor.close();
		return 0;
	}

	public int CheckFCSetting(String upc) {
		String sql = "select * from t_flip_chart where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return 0;
		if (cursor.moveToNext()) {
			cursor.close();
			return 1;
		}
		cursor.close();
		return 0;
	}

	public int CheckPriceSetting(String upc) {
		String sql = "select * from t_price_setting where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return 0;
		if (cursor.moveToNext()) {
			cursor.close();
			return 1;
		}
		cursor.close();
		return 0;
	}

	public int CheckTareSetting(String upc) {
		String sql = "select * from t_item_tare_setting where F01 = '" + upc
				+ "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return 0;
		if (cursor.moveToNext()) {
			cursor.close();
			return 1;
		}
		cursor.close();
		return 0;
	}

	public int GetFlipChartOrderNo(String upc) {
		String sql = "select fc_order from t_flip_chart where F01 = '" + upc
				+ "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		int order = 0;
		if (cursor == null)
			return order;
		if (cursor.moveToNext()) {
			order = cursor.getInt(0);
			cursor.close();
			return order;
		}
		cursor.close();
		return order;
	}

	public int GetFlipChartGroup(String upc) {
		String sql = "select fc_group from t_flip_chart where F01 = '" + upc
				+ "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		int group = 0;
		if (cursor == null)
			return group;
		if (cursor.moveToNext()) {
			group = cursor.getInt(0);
			cursor.close();
			return group;
		}
		cursor.close();
		return group;
	}

	public boolean CheckItemSetting(String upc) {
		String sql = "select * from t_item_setting where F01 = '" + upc + "'";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return false;
		if (cursor.moveToNext()) {
			cursor.close();
			return true;
		}
		cursor.close();
		return false;
	}

	public int postDeleteSetOrder(int order, int group) {
		String sql = "update t_flip_chart set fc_order = fc_order - 1,"
				+ "update_datetime = strftime('%Y%m%d%H%M%S','now')"
				+ " where fc_group = " + group + " and fc_order > " + order;
		String errorString = DBHelper.execSQL(sql);
		if (errorString.length() > 0) {
			return 0;
		} else {
			return 1;
		}
	}

	public void ImportAddData(ImportData importData) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"INSERT INTO "
						+ DatabaseSchema.ItemSetting.SCHEMA_NAME
						+ "("
						+ DatabaseSchema.ItemSetting.ITEM_UPC
						+ ","
						+ DatabaseSchema.ItemSetting.DESCRIPTOR
						+ ","
						+ DatabaseSchema.ItemSetting.EXPANDED_DESCRIPTION
						+ ","
						+ DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY
						+ ","
						+ DatabaseSchema.ItemSetting.UNIT_PRICE
						+ ","
						+ DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR
						+ ","
						+ DatabaseSchema.ItemSetting.UNIT_OF_MEASURE
						+ ","
						+ DatabaseSchema.ItemSetting.DEPARTMENT_NUMBER
						+ ","
						+ DatabaseSchema.ItemSetting.FOLLOW_DEPARTMENT
						+ ","
						+ DatabaseSchema.ItemSetting.TAX1
						+ ","
						+ DatabaseSchema.ItemSetting.TAX2
						+ ","
						+ DatabaseSchema.ItemSetting.TAX3
						+ ","
						+ DatabaseSchema.ItemSetting.TAX4
						+ ","
						+ DatabaseSchema.ItemSetting.TAX5
						+ ","
						+ DatabaseSchema.ItemSetting.TAX6
						+ ","
						+ DatabaseSchema.ItemSetting.FOOD_STAMP
						+ ","
						+ DatabaseSchema.ItemSetting.ITEM_DISCOUNT_FLAG
						+ ","
						+ DatabaseSchema.ItemSetting.SCALE_OR_WEIGHT
						+ ","
						+ DatabaseSchema.ItemSetting.WIC_ELIGIBLE
						+ ","
						+ DatabaseSchema.ItemSetting.CVV_ELIGIBILITY
						+ ","
						+ DatabaseSchema.ItemSetting.IS_LOYALTY
						+ ","
						+ "create_datetime,update_datetime)"
						+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'))",
				new Object[] { importData.getUpc(), importData.getName(),
						importData.getDesc(), importData.getPrice_multple(),
						importData.getPrice(), importData.getNet_wt(),
						importData.getUnit_of_measure(),
						importData.getDepartment(),
						importData.getFollow_dept(), importData.getTax_1(),
						importData.getTax_2(), importData.getTax_3(),
						importData.getTax_4(), importData.getTax_5(),
						importData.getTax_6(), importData.getFood_stamp(),
						importData.getNo_discount(), importData.getScalable(),
						importData.getWic(), importData.getCvv(),
						importData.getLoyalty() });
	}

	public void ImportUpdataData(ImportData importData) {
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		db.execSQL(
				"Update "
						+ DatabaseSchema.ItemSetting.SCHEMA_NAME
						+ " set "
						+ DatabaseSchema.ItemSetting.DESCRIPTOR
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.EXPANDED_DESCRIPTION
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.CURRENT_PRICE_QTY
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.UNIT_PRICE
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.UNIT_PRICE_FACTOR
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.UNIT_OF_MEASURE
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.DEPARTMENT_NUMBER
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.FOLLOW_DEPARTMENT
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX1
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX2
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX3
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX4
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX5
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.TAX6
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.FOOD_STAMP
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.ITEM_DISCOUNT_FLAG
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.SCALE_OR_WEIGHT
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.WIC_ELIGIBLE
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.CVV_ELIGIBILITY
						+ " = ?,"
						+ DatabaseSchema.ItemSetting.IS_LOYALTY
						+ " = ?,"
						+ "update_datetime = strftime('%Y%m%d%H%M%S','now') where "
						+ DatabaseSchema.ItemSetting.ITEM_UPC + " = ?",
				new Object[] { importData.getName(), importData.getDesc(),
						importData.getPrice_multple(), importData.getPrice(),
						importData.getNet_wt(),
						importData.getUnit_of_measure(),
						importData.getDepartment(),
						importData.getFollow_dept(), importData.getTax_1(),
						importData.getTax_2(), importData.getTax_3(),
						importData.getTax_4(), importData.getTax_5(),
						importData.getTax_6(), importData.getFood_stamp(),
						importData.getNo_discount(), importData.getScalable(),
						importData.getWic(), importData.getCvv(),
						importData.getLoyalty(), importData.getUpc() });
	}

	public boolean CheckAgeNo(int no) {
		String sql = "select * from t_item_setting where is_age_ver = " + no;
		String sql2 = "select * from t_department_setting where ds_age_ver = "
				+ no;
		db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor.moveToNext()) {
			cursor.close();
			return true;
		}
		cursor.close();
		db = DBHelper.getWritableDatabase();
		Cursor cursor2 = db.rawQuery(sql2, null);
		if (cursor2.moveToNext()) {
			cursor2.close();
			return true;
		}
		cursor2.close();
		return false;
	}

	public boolean Delete(String upc) {
		try {
			db = DBHelper.getWritableDatabase();
			db.execSQL("Delete from t_item_setting where F01 = '" + upc + "'");
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}

	public boolean DeleteItemTareSetting(String upc) {
		try {
			db = DBHelper.getWritableDatabase();
			db.execSQL("Delete from t_item_tare_setting where F01 = '" + upc
					+ "'");
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}

	public boolean InsertItemTareSetting(String upc, int tareId) {
		try {
			db = DBHelper.getWritableDatabase();
			db.execSQL("insert into t_item_tare_setting"
					+ "(F01,it_tare_no,create_datetime,update_datetime)"
					+ "values('"
					+ upc
					+ "',"
					+ tareId
					+ ",strftime('%Y%m%d%H%M%S','now'),strftime('%Y%m%d%H%M%S','now'))");
		} catch (Exception e) {
			// TODO: handle exception
			return false;
		}
		return true;
	}
	
	public int getItemCount() {
		int count = 0;
		String sql = "select count(F01) from t_item_setting";
		SQLiteDatabase db = DBHelper.getWritableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		if (cursor == null)
			return count;
		if (cursor.moveToNext()) {
			count =  cursor.getInt(0);
		}
		cursor.close();
		return count;
	}
}
